package com.clkj.xiuzheng.dao.shangcheng;


import com.clkj.xiuzheng.pojo.Product;
import com.clkj.xiuzheng.pojo.shangcheng.SProduct;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;
import tk.mybatis.mapper.common.Mapper;

import java.util.List;

@Repository
public interface ScProductMapper extends Mapper<SProduct> {
    @Select("SELECT c1.category_name categoryOneName, c2.category_name categoryTwoName,c3.category_name categoryThreeName\n" +
            "FROM t_xz_product p\n" +
            "LEFT JOIN t_xz_productCategory c1 ON p.category_one_id = c1.product_category_id \n" +
            "LEFT JOIN t_xz_productCategory c2 ON p.category_two_id = c2.product_category_id \n" +
            "LEFT JOIN t_xz_productCategory c3 ON p.category_three_id = c3.product_category_id\n" +
            "AND p.product_id = #{id}")
    SProduct selectCategoryNameById(@Param("id") Integer id);


    @Select("SELECT MAX(f.product_price) maxPrice,MIN(f.product_price) minPrice FROM t_xz_product p LEFT JOIN t_xz_productFormat f  on  p.product_id = f.product_id where p.product_id = #{id}")
    SProduct getPriceByProductId(@Param("id") Integer id);

    @Insert("insert into t_xz_product  " +
            "values(#{productName},0,#{productDescription},#{productImage},#{factoryId},1,#{productKind},#{categoryOneId},#{categoryTwoId},#{categoryThreeId},#{companyId},#{productVideo},#{productBrandId},#{productSaleType},#{maxPrice},#{maxSource}) ")
    @Options(useGeneratedKeys = true, keyProperty = "productId")
    void insertProduct(SProduct product);

    @Select({"<script>",
            "SELECT",
            "p.*,",
            "b.brand_name,",
            "c1.category_name AS category_one_name,",
            "c2.category_name AS category_two_name,",
            "c3.category_name AS category_three_name,",
            "fa.factory_name AS factory_name,",
            "SUM(f.product_sales) AS ProductSales,",
            "SUM(f.product_num) AS ProductNum",
            "FROM",
            "t_xz_product p",
            "LEFT JOIN",
            "t_xz_productBrand b ON p.product_brand_id = b.brand_id",
            "LEFT JOIN",
            "t_xz_productCategory c1 ON p.category_one_id = c1.product_category_id",
            "LEFT JOIN",
            "t_xz_productCategory c2 ON p.category_two_id = c2.product_category_id",
            "LEFT JOIN",
            "t_xz_productCategory c3 ON p.category_three_id = c3.product_category_id",
            "LEFT JOIN",
            "t_xz_productFormat f ON p.product_id = f.product_id",
            "LEFT JOIN",
            "t_xz_productFactory fa ON p.factory_id = fa.factory_id",
            "where 1=1",
            "<if test = 'companyId !=null'>",
            "and p.company_id = #{companyId}",
            "</if>",
            "<if test = 'productName !=null'>",
            "and p.product_name like '%${productName}%' ",
            "</if>",
            "<if test = 'productStatus !=null'>",
            "and p.product_status = #{productStatus}",
            "</if>",
            "<if test = 'productKind !=null'>",
            "and p.product_kind =#{productKind}",
            "</if>",
            "<if test = 'categoryOneId !=null'>",
            "and p.category_one_id =#{categoryOneId}",
            "</if>",
            "<if test = 'categoryTwoId !=null'>",
            "and p.category_two_id =#{categoryTwoId}",
            "</if>",
            "<if test = 'categorythreeId !=null'>",
            "and p.category_three_id =#{categorythreeId}",
            "</if>",
            "GROUP BY",
            "p.product_id,p.category_one_id,p.category_three_id,p.category_two_id,p.factory_id,p.product_brand_id,p.product_buy_num,p.product_description,p.product_image,p.product_name,p.product_sale_type,p.product_status,p.product_kind,p.product_video,p.company_id,b.brand_name, c1.category_name, c2.category_name, c3.category_name,fa.factory_name,p.max_price,p.max_source",
            "</script>"
    })
    List<SProduct> getProductListByCompanyId(@Param("companyId") Integer companyId, @Param("productName") String productName, @Param("productStatus") Integer productStatus, @Param("productKind") Integer productKind, @Param("categoryOneId") Integer categoryOneId, @Param("categoryTwoId") Integer categoryTwoId, @Param("categorythreeId") Integer categorythreeId);

    @Select("SELECT \n" +
            "    p.*,\n" +
            "    b.brand_name,\n" +
            "    c1.category_name AS category_one_name,\n" +
            "    c2.category_name AS category_two_name,\n" +
            "    c3.category_name AS category_three_name,\n" +
            "\t\tfa.factory_name AS factory_name\n" +
            "FROM \n" +
            "    t_xz_product p\n" +
            "LEFT JOIN \n" +
            "    t_xz_productBrand b ON p.product_brand_id = b.brand_id\n" +
            "LEFT JOIN \n" +
            "    t_xz_productCategory c1 ON p.category_one_id = c1.product_category_id\n" +
            "LEFT JOIN \n" +
            "    t_xz_productCategory c2 ON p.category_two_id = c2.product_category_id\n" +
            "LEFT JOIN \n" +
            "    t_xz_productCategory c3 ON p.category_three_id = c3.product_category_id\n" +
            "LEFT JOIN\n" +
            "    t_xz_productFactory fa ON p.factory_id = fa.factory_id "+
             "where p.product_id = #{productId}")
    SProduct getProductById(Integer productId);

    @Select("select * from " +
            "(select *,row_number() over(partition by product_sale_type order by product_sale_type) as rownum  from t_xz_product where product_sale_type in (1,2) and product_status  = 1  and product_kind = 0 ) as " +
            "t_cl_product where rownum<5")
    List<SProduct> listEightPro();


    @Select(" update t_xz_product set product_buy_num +=#{num} where product_id = #{productId}")
    void updateProductBuyNum(Integer num,Integer productId);
}
